package cn.cocho.custom.yunCashierDf.utils.execl;

/**
 * Created by lp on 2017/5/16 0016.
 */


import cn.cocho.custom.yunCashierDf.utils.DateUtil;
import cn.cocho.custom.yunCashierDf.utils.StringUtil;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;




/**
 * 基于POI实现的Excel工具类
 *
 * @author lp
 *
 */
public class XssfExcelHelper extends ExcelHelper {

    private static XssfExcelHelper instance = null; // 单例对象

    private File file; // 操作文件

    private HttpServletResponse response; // 操作文件
    /**
     * 私有化构造方法
     *
     * @param file
     *            文件对象
     */
    private XssfExcelHelper(File file) {
        super();
        this.file = file;
    }
    private XssfExcelHelper(HttpServletResponse response) {
        super();
        this.response = response;
    }
    public File getFile() {
        return file;
    }

    public void setFile(File file) {
        this.file = file;
    }

    /**
     * 获取单例对象并进行初始化
     *
     * @param file
     *            文件对象
     * @return 返回初始化后的单例对象
     */
    public static XssfExcelHelper getInstance(File file) {
        if (instance == null) {
            // 当单例对象为null时进入同步代码块
            synchronized (XssfExcelHelper.class) {
                // 再次判断单例对象是否为null，防止多线程访问时多次生成对象
                if (instance == null) {
                    instance = new XssfExcelHelper(file);
                }
            }
        } else {
            // 如果操作的文件对象不同，则重置文件对象
            if (!file.equals(instance.getFile())) {
                instance.setFile(file);
            }
        }
        return instance;
    }
    /**
     * 获取单例对象并进行初始化
     *
     * @param response
     *            服务器响应
     * @return 返回初始化后的单例对象
     */
    public static XssfExcelHelper getExcel(HttpServletResponse response) {
        if (instance == null) {
            // 当单例对象为null时进入同步代码块
            synchronized (XssfExcelHelper.class) {
                // 再次判断单例对象是否为null，防止多线程访问时多次生成对象
                if (instance == null) {
                    instance = new XssfExcelHelper(response);
                }
            }
        }
        return instance;
    }
    /**
     * 获取单例对象并进行初始化
     *
     * @param filePath
     *            文件路径
     * @return 返回初始化后的单例对象
     */
    public static XssfExcelHelper getInstance(String filePath) {
        return getInstance(new File(filePath));
    }

    @Override
    public <T> List<T> readExcel(Class<T> clazz,SpecialHandling specialHandling,int sheetNo, boolean hasTitle) throws Exception {
        List<T> dataModels = new ArrayList<T>();
        // 获取excel工作簿
        XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(file));
        XSSFSheet sheet = workbook.getSheetAt(sheetNo);
        List<String> execlHead=getHead(sheet);

        Map<String, String> fieldForCloumnMap=specialHandling.getFieldForCloumnMap();

        int start = sheet.getFirstRowNum() + (hasTitle ? 1 : 0); // 如果有标题则从第二行开始
        for (int i = start; i <= sheet.getLastRowNum(); i++) {
            XSSFRow row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            // 生成实例并通过反射调用setter方法
            T target = clazz.newInstance();

            String ObjectFieldName =null;
            for (int j = 0; j < execlHead.size(); j++) {
                ObjectFieldName =fieldForCloumnMap.get(execlHead.get(j));
                if(StringUtil.isNotBlank(ObjectFieldName)){
                    XSSFCell cell=row.getCell(j);
                    if(cell!=null){
                        Field field = clazz.getDeclaredField(ObjectFieldName);
                        String content = getCellContent(cell,field.getType());
                        if (isDateType(clazz, ObjectFieldName)) {
                            // 如果属性是日期类型则将内容转换成日期对象
                            ReflectUtil.invokeSetter(target, ObjectFieldName,specialHandling.toDateByExeclDateFormat(content));
                        } else {
                            ReflectUtil.invokeSetter(target, ObjectFieldName,parseValueWithType(content, field.getType()));
                        }
                    }


                }

            }
            target=specialHandling.doLastHandle(target,clazz);
            dataModels.add(target);
        }

        return dataModels;
    }

    /**
     * 获取传来的sheet的 列名的有序集合
     * @param sheet execl 表格的sheet
     * @return 列名集合
     */
    public List<String> getHead(XSSFSheet sheet){
        List<String> headList=new ArrayList<String>();
        XSSFRow headRow = sheet.getRow(0);
        for (int i = 0; i < headRow.getLastCellNum(); i++) {
            if(headRow.getCell(i).getStringCellValue() != ""){ //wqj 加列名为空的判断
                headList.add(headRow.getCell(i).getStringCellValue());
            }else{
                break;
            }

        }
        return headList;
    }

    @Override
    public <T> void writeExcel(Class<T> clazz, List<T> dataModels,
                               String[] fieldNames, String[] titles) throws Exception {
        XSSFWorkbook workbook = null;
        if(file!=null){
            // 检测文件是否存在，如果存在则修改文件，否则创建文件
            if (file.exists()) {
                FileInputStream fis = new FileInputStream(file);
                workbook = new XSSFWorkbook(fis);
            } else {
                workbook = new XSSFWorkbook();
            }
        }else {
            workbook = new XSSFWorkbook();
        }

        // 根据当前工作表数量创建相应编号的工作表
        String sheetName = DateUtil.convert2String(new Date(), "yyyyMMddHHmmssSS");
        //String sheetName="数据字典";
        XSSFSheet sheet = workbook.createSheet(sheetName);
        XSSFRow headRow = sheet.createRow(0);
        // 添加表格标题
        for (int i = 0; i < titles.length; i++) {
            XSSFCell cell = headRow.createCell(i);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(titles[i]);
            // 设置字体加粗
            XSSFCellStyle cellStyle = workbook.createCellStyle();
            XSSFFont font = workbook.createFont();
            font.setBoldweight(Font.BOLDWEIGHT_BOLD);
            cellStyle.setFont(font);
            // 设置自动换行
            cellStyle.setWrapText(true);
            cell.setCellStyle(cellStyle);
            // 设置单元格宽度
            sheet.setColumnWidth(i, titles[i].length() * 1000);
        }
        // 添加表格内容
        for (int i = 0; i < dataModels.size(); i++) {
            T target = dataModels.get(i);
            XSSFRow row = sheet.createRow(i + 1);
            // 遍历属性列表
            for (int j = 0; j < fieldNames.length; j++) {
                // 通过反射获取属性的值域
                String fieldName = fieldNames[j];
                if (fieldName == null || UID.equals(fieldName)) {
                    continue; // 过滤serialVersionUID属性
                }
                Object result = ReflectUtil.invokeGetter(target, fieldName);
                XSSFCell cell = row.createCell(j);
                cell.setCellValue(StringUtil.toString(result));
                // 如果是日期类型则进行格式化处理
                if (isDateType(clazz, fieldName)) {
                    cell.setCellValue(DateUtil.convert2String((Date) result,"yyyy-MM-dd H:m:s"));
                }
                if("createTime".equals(fieldName)){
                    cell.setCellValue(DateUtil.convert2String((Date) new Date(Long.parseLong(result.toString())),"yyyy-MM-dd H:m:s"));
                }
                /*if("txamt".equals(fieldName)){
                    cell.setCellValue((Long.parseLong(result.toString())/100)+"");
                }*/
            }
        }
        if(file!=null){
            // 将数据写到磁盘上
            FileOutputStream fos = new FileOutputStream(file);
            try {
                workbook.write(new FileOutputStream(file));
            } finally {
                if (fos != null) {
                    fos.close(); // 不管是否有异常发生都关闭文件输出流
                }
            }
        }else {
            //把数据导入到浏览器
            String fileName=DateUtil.convert2String(new Date(), "yyyyMMddHHmmssSS");
//			String fileName="数据字典数据";

            //System.out.println(response.getCharacterEncoding());
            response.reset();
            //指定响应以UTF-8格式编码内容
            response.setCharacterEncoding("UTF-8");
            //通知浏览器以何种编码格式打开内容
            response.setHeader("Content-Type", "text/html;charset=UTF-8");
            //response.setContentType("application/vnd.ms-excel");        //改成输出excel文件
            response.setContentType("application/x-download; charset=UTF-8");
            response.setHeader("Content-disposition","attachment; filename="+ fileName +".xlsx" );
            //System.out.println(response.getCharacterEncoding());
            //response.setHeader("Content-Disposition", "attachment;filename="+new String(filename.getBytes("gbk"), "iso8859-1")+".xls");
            OutputStream out=response.getOutputStream();
//			for (int i=0;i<dataModels.size();i++){
//				Codelist codelist=(Codelist)dataModels.get(i);
//				System.out.println(codelist.getCodename()+"-----------"+codelist.getCodeTypeName());
//			}
            workbook.write(out);
        }
    }

    @Override
    protected <T> Object parseValueWithType(String value, Class<?> type) {
        // 由于Excel2007的numeric类型只返回double型，所以对于类型为整型的属性，要提前对numeric字符串进行转换
        if (Byte.TYPE == type || Short.TYPE == type || Short.TYPE == type
                || Long.TYPE == type ) {
            value = String.valueOf((long) Double.parseDouble(value));
        }
        return super.parseValueWithType(value, type);
    }

    /**
     * 获取单元格的内容
     *
     * @param cell
     *            单元格
     * @return 返回单元格内容
     */
    private String getCellContent(XSSFCell cell, Class<?> type) {
        StringBuffer buffer = new StringBuffer();

        if(cell.getCellType()==XSSFCell.CELL_TYPE_NUMERIC && (String.class==type || Byte.TYPE == type || Short.TYPE == type || Integer.class == type || Long.TYPE == type)){
            cell.setCellType(XSSFCell.CELL_TYPE_STRING);
            return buffer.append(cell.getStringCellValue()).toString();
        }

        switch (cell.getCellType()) {
            case XSSFCell.CELL_TYPE_NUMERIC : // 数字
                buffer.append(cell.getNumericCellValue());
                break;
            case XSSFCell.CELL_TYPE_BOOLEAN : // 布尔
                buffer.append(cell.getBooleanCellValue());
                break;
            case XSSFCell.CELL_TYPE_FORMULA : // 公式
                buffer.append(cell.getCellFormula());
                break;
            case XSSFCell.CELL_TYPE_STRING : // 字符串
                buffer.append(cell.getStringCellValue());
                break;
            case XSSFCell.CELL_TYPE_BLANK : // 空值
            case XSSFCell.CELL_TYPE_ERROR : // 故障
            default :
                break;
        }
        return buffer.toString();
    }
}